﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using Excel = Microsoft.Office.Interop.Excel;
using System.Web.UI.WebControls;
using System.IO;
using System.Drawing;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Runtime.InteropServices;
/// <summary>
/// Summary description for ExcelExport
/// </summary>
public class ExcelExport
{
    [DllImport("user32")]
    private static extern int EndTask(IntPtr hwnd, bool fshutdown, bool force);
    Microsoft.Office.Interop.Excel.Application ExcelApp=null;
    Microsoft.Office.Interop.Excel.Workbook ExcelBook=null;
    Microsoft.Office.Interop.Excel.Worksheet ExcelSheet=null;    
    Excel.Range ChartRange;
    object MisVal = System.Reflection.Missing.Value;
    public string ExportGridToExcel(string FileName, string HeaderText, GridView gridview)
    {
        string Message = "";
        ExcelApp = new Microsoft.Office.Interop.Excel.Application();
        ExcelBook = (Microsoft.Office.Interop.Excel.Workbook)ExcelApp.Workbooks.Add(1);
        ExcelSheet = (Microsoft.Office.Interop.Excel.Worksheet)ExcelBook.ActiveSheet;
        try
        {
            if (gridview.Rows.Count > 0)
            {
                int i = 0;
                int j = 0;
                //Create Object Of Excel                
                ExcelSheet.get_Range(GetExcelColumnName(1) + "2", GetExcelColumnName(gridview.Columns.Count) + "2").Merge(false);
                SetRangeName(GetExcelColumnName(1) + "2", GetExcelColumnName(gridview.Columns.Count) + "2", true, HeaderText, 3, 3);
                ChartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                ChartRange.Font.ColorIndex = 2;
                ChartRange.BorderAround(Excel.XlLineStyle.xlDouble, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
                ChartRange.Borders.ColorIndex = 2;

                //Export Header
                for (i = 1; i <= gridview.Columns.Count; i++)
                {
                    ExcelSheet.Cells[3, i] = gridview.Columns[i - 1].HeaderText.ToUpper();
                    SetBorder(GetExcelColumnName(i) + "3", GetExcelColumnName(i) + "3");
                    if (i == 1)
                    {
                        ChartRange = ExcelSheet.get_Range(GetExcelColumnName(1) + "3", GetExcelColumnName(1) + "3");
                        ChartRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
                    }
                    else if (i == gridview.Columns.Count)
                    {
                        ChartRange = ExcelSheet.get_Range(GetExcelColumnName(gridview.Columns.Count) + "3", GetExcelColumnName(gridview.Columns.Count) + "3");
                        ChartRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
                    }
                    ChartRange.get_Range(GetExcelColumnName(i) + "3", GetExcelColumnName(i) + "3");
                    ChartRange.Font.Bold = true;
                    ChartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
                }

                //Export Data
                for (i = 1; i <= gridview.Rows.Count; i++)
                {
                    for (j = 1; j <= gridview.Columns.Count; j++)
                    {
                        if (IsDataTypeValid(gridview.Rows[i - 1].Cells[j - 1].Text.ToString().Trim(), System.Globalization.NumberStyles.Integer) || IsDataTypeValid(gridview.Rows[i - 1].Cells[j - 1].Text.ToString().Trim(), System.Globalization.NumberStyles.Float))
                        {
                            SetNumberFormat(GetExcelColumnName(j) + (i + 3).ToString(), GetExcelColumnName(j) + (i + 3).ToString());
                        }
                        if (gridview.Rows[i - 1].Cells[j - 1].Text.Trim().ToString() != "&nbsp;")
                        {
                            ExcelSheet.Cells[i + 3, j] = gridview.Rows[i - 1].Cells[j - 1].Text.Trim().ToString();
                        }
                        SetBorder(GetExcelColumnName(j) + (i + 3).ToString(), GetExcelColumnName(j) + (i + 3).ToString());
                        if (j == 1)
                        {
                            ChartRange = ExcelSheet.get_Range(GetExcelColumnName(1) + (i + 3).ToString(), GetExcelColumnName(1) + (i + 3).ToString());
                            ChartRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeLeft].LineStyle = Excel.XlLineStyle.xlDouble;
                        }
                        else if (j == gridview.Columns.Count)
                        {
                            ChartRange = ExcelSheet.get_Range(GetExcelColumnName(gridview.Columns.Count) + (i + 3).ToString(), GetExcelColumnName(gridview.Columns.Count) + (i + 3).ToString());
                            ChartRange.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlEdgeRight].LineStyle = Excel.XlLineStyle.xlDouble;
                        }
                    }
                    if (i == gridview.Rows.Count)
                    {
                    }
                }
                ChartRange.get_Range(GetExcelColumnName(1) + (i + 5).ToString(), GetExcelColumnName(1) + (i + 5).ToString());
                ChartRange.Font.Bold = true;
                ChartRange.get_Range(GetExcelColumnName(7) + (i + 5).ToString(), GetExcelColumnName(7) + (i + 5).ToString());
                ChartRange.Font.Bold = true;

                Microsoft.Office.Interop.Excel.Range MyRange = ExcelSheet.get_Range(ExcelSheet.Cells[1, 1], ExcelSheet.Cells[gridview.Rows.Count + 1, gridview.Columns.Count]);
                Microsoft.Office.Interop.Excel.Font FontNameX = MyRange.Font;
                MyRange = ExcelSheet.get_Range(ExcelSheet.Cells[3, 1], ExcelSheet.Cells[3, gridview.Columns.Count]);
                FontNameX = MyRange.Font;
                MyRange.EntireColumn.AutoFit();
                MyRange.EntireRow.AutoFit();

                string DestinationPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop) + "\\DisAccount Files";
                if (!Directory.Exists(DestinationPath))
                {
                    Directory.CreateDirectory(DestinationPath);
                }
                ExcelBook.SaveAs(DestinationPath + "\\" + FileName + "_" + DateTime.Now.ToString("") + ".xls", Excel.XlFileFormat.xlWorkbookNormal, MisVal, MisVal, MisVal, MisVal, Excel.XlSaveAsAccessMode.xlNoChange, MisVal, MisVal, MisVal, MisVal, MisVal);
                ExcelApp.Quit();
                IntPtr XlHwnd = new IntPtr(ExcelApp.Hwnd);
                EndTask(XlHwnd, false, true);
                Message = "Successfully Converted.";
            }
        }
        catch (Exception ex)
        {
            Message = ex.Message;
        }
        finally
        {
            GC.Collect();            
        }
        return Message;
    }
    private void SetNumberFormat(string Row, string Column)
    {
        ChartRange = ExcelSheet.get_Range(Row, Column);
        ChartRange.NumberFormat = "@";
    }
    private bool IsDataTypeValid(string value, System.Globalization.NumberStyles NumberStyle)
    {
        Double result;
        return Double.TryParse(value, NumberStyle, System.Globalization.CultureInfo.CurrentCulture, out result);
    }
    private void SetBorder(string Range1, string Range2)
    {
        ChartRange = ExcelSheet.get_Range(Range1, Range2);
        ChartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlThin, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);
    }
    private void SetRangeName(string Range1, string Range2, bool b, string Name, int VerticalAlign, int HorizontalAlign)
    {
        ChartRange = ExcelSheet.get_Range(Range1, Range2);
        ChartRange.Font.Bold = b;
        ChartRange.FormulaR1C1 = Name;
        ChartRange.VerticalAlignment = VerticalAlign;
        ChartRange.HorizontalAlignment = HorizontalAlign;
    }
    private string GetExcelColumnName(int ColumnNumber)
    {
        int dividend = ColumnNumber;
        string ColumnName = string.Empty;
        int Module;
        while (dividend > 0)
        {
            Module = (dividend - 1) % 26;
            ColumnName = Convert.ToChar(65 + Module).ToString() + ColumnName;
            dividend = (int)((dividend - Module) / 26);
        }
        return ColumnName;
    }
	public ExcelExport()
	{
		//
		// TODO: Add constructor logic here
		//
	}
}